GeoDataFrame#

This is a team work from Python Intermediate Course. You can check out how to import data from Github, convert to PandaDataFrame and GeoDataFrame, verify missing values, manipulate and merge data, and make interactive maps according to the respective request.

#!pip install folium

#Librerías
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt 
import chardet

import folium as fm
from folium import Marker, GeoJson
from folium.plugins import MarkerCluster, HeatMap, StripePattern

import geopandas as gpd
from geopandas import GeoSeries
from shapely.geometry import Point, LineString


import branca as br 

from IPython.display import display, HTML

display(HTML(data="""
<style>
    div#notebook-container    { width: 95%; }
    div#menubar-container     { width: 65%; }
    div#maintoolbar-container { width: 99%; }a
</style>
"""))

2. Make an interactive map of markes using visual_html function and show total vacancies, applicants, entrants, and enrolled information in a table popup. Additionally, use “university” as icon.#

#Creamos la función
def visual_html(i):
 
    # information by institute 
    total_vacancies = inst['ltimoden_metaatencion'].iloc[i]                             
    applicants = inst['cuentadeid_postulante_procesoadm'].iloc[i]                           
    entrants = inst['sumaden_flagingresante'].iloc[i]     
    enrolled = inst['sumaden_flagmatriculado'].iloc[i]   
    
    # Color by each column of table 
    
    left_col_colour = "#9e063b"
    right_col_colour = "#fcdce7"
    
    html = """<!DOCTYPE html>
<html>

<head>
    <p> Instituto </p>

</head>
    <table style="height: 126px; width: 350px;">  <!-- Comment: Create a teable. -->

<!-- Add information  -->

<tbody> 
<tr>

<!-- Add color by column -->

<td style="background-color: """+ left_col_colour +""";"><span style="color: #ffffff;">Vacantes totales</span></td>
<td style="width: 150px;background-color: """+ right_col_colour +""";">{}</td>""".format(total_vacancies) + """
</tr>
<tr>
<td style="background-color: """+ left_col_colour +""";"><span style="color: #ffffff;">Postulantes</span></td>
<td style="width: 150px;background-color: """+ right_col_colour +""";">{}</td>""".format(applicants) + """
</tr>
<tr>
<td style="background-color: """+ left_col_colour +""";"><span style="color: #ffffff;">Ingresantes</span></td>
<td style="width: 150px;background-color: """+ right_col_colour +""";">{}</td>""".format(entrants) + """
</tr>
<tr>
<td style="background-color: """+ left_col_colour +""";"><span style="color: #ffffff;">Matriculados</span></td>
<td style="width: 150px;background-color: """+ right_col_colour +""";">{}</td>""".format(enrolled) + """
</tr>

</tbody>
</table>
</html>
"""
    return html

ubication = inst['nlat_ie'].mean(), inst['nlong_ie'].mean()  # Average point

inst_ubi = fm.Map(location = ubication, zoom_start=7)

for i in range(0,len(inst)):
    html = visual_html(i)

    iframe = br.element.IFrame(html=html,width=350,height=300)
    popup = fm.Popup(iframe, parse_html=True)
    
    fm.Marker([inst['nlat_ie'].iloc[i],inst['nlong_ie'].iloc[i]],
                  popup=popup, icon=fm.Icon(color= 'blue', icon='fa-building-columns', prefix="fa")).add_to(inst_ubi)

inst_ubi.save("institutos.html")
inst_ubi
Make this Notebook Trusted to load map: File -> Trust Notebook

3. Generate the ratio of applicants by vacancies and store it in a new columns called ratio_applicants_vacancies ( total applicants / total vacancies ). Then, generate a cluster map with two groups of marker clusters. The first group should be compound by those institutes where there were more applicants than vacancies. Name this group as Applicant Surplus. The second group should be compound by those institutes where there were more vacancies than applicants. Name this group as Applicant Deficit.#

#Generamos la nueva variable
inst['Ratio Postulantes/Vacantes'] = inst['cuentadeid_postulante_procesoadm'] / inst['ltimoden_metaatencion']
recodificar = lambda x: 'Applicant Surplus' if x > 1 else 'Applicant Deficit'
inst['Clusters'] = inst['Ratio Postulantes/Vacantes'].apply(recodificar) 
#Mostrar la base de datos    
inst
cod_mod ltimoden_metaatencion cuentadeid_postulante_procesoadm sumaden_flagingresante sumaden_flagmatriculado ratio ratio1 dif dif1 nlat_ie ... Dblock2km Dblock3km Dblock5km Dblock10km Dblock20km Dblock30km _merge geometry Ratio Postulantes/Vacantes Clusters
0 207597 307 481 302 301 0.980456 1.566775 -6 174 -13.52107 ... 742184 494123 296073 148036 7418 4912 matched (3) POINT (-71.97748 -13.52107) 1.566775 Applicant Surplus
1 207613 240 502 204 204 0.850000 2.091667 -36 262 -13.63997 ... 748235 499156 299094 149047 7423 4915 matched (3) POINT (-72.88261 -13.63997) 2.091667 Applicant Surplus
2 239970 537 1047 484 476 0.886406 1.949721 -61 510 -15.47827 ... 850081 566054 340032 170016 8508 5605 matched (3) POINT (-70.12756 -15.47827) 1.949721 Applicant Surplus
3 262311 423 526 353 349 0.825059 1.243499 -74 103 -6.23041 ... 339512 226341 135205 67102 3351 2234 matched (3) POINT (-77.86943 -6.23041) 1.243499 Applicant Surplus
4 273979 425 544 411 405 0.952941 1.280000 -20 119 -6.49004 ... 353428 235285 141171 70085 3542 2328 matched (3) POINT (-76.36539 -6.49004) 1.280000 Applicant Surplus
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
210 1468594 1566 250 241 202 0.128991 0.159642 -1364 -1316 -12.02987 ... 659467 439311 263187 131093 6546 4331 matched (3) POINT (-77.05831 -12.02987) 0.159642 Applicant Deficit
211 1468610 284 74 71 71 0.250000 0.260563 -213 -210 -12.08215 ... 662467 441311 265186 132093 6646 4431 matched (3) POINT (-77.05295 -12.08215) 0.260563 Applicant Deficit
212 1545623 210 266 184 177 0.842857 1.266667 -33 56 -16.13940 ... 887104 591069 354041 177020 8810 5906 matched (3) POINT (-70.53580 -16.13940) 1.266667 Applicant Surplus
213 1595495 206 144 141 137 0.665049 0.699029 -69 -62 -18.01370 ... 990088 660059 396035 198017 9908 6605 matched (3) POINT (-70.25080 -18.01370) 0.699029 Applicant Deficit
214 1637859 180 134 125 125 0.694444 0.744444 -55 -46 -13.63631 ... 748234 499156 299093 149046 7423 4915 matched (3) POINT (-72.87916 -13.63631) 0.744444 Applicant Deficit

215 rows × 24 columns

#Generamos el mapa
ubication2 = inst['nlat_ie'].mean(), inst['nlong_ie'].mean()

inst_ubi2 = fm.Map(location = ubication2, zoom_start=8)

# Creamos dos clusters para cada grupo
mc1 = MarkerCluster(name='Applicant Surplus')
mc2 = MarkerCluster(name='Applicant Deficit')

# Iteramos a través de los datos y añadimos los marcadores a los clusters correspondientes
for index, row in inst.iterrows():
    if row['Clusters'] == 'Applicant Surplus':
        fm.Marker(location=[row['nlat_ie'], row['nlong_ie']], popup=['Ratio Postulantes/Vacantes']).add_to(mc1)
    elif row['Clusters'] == 'Applicant Deficit':
        fm.Marker(location=[row['nlat_ie'], row['nlong_ie']], popup=['Ratio Postulantes/Vacantes']).add_to(mc2)

# Añadimos los clusters al mapa y lo mostramos
mc1.add_to(inst_ubi2)
mc2.add_to(inst_ubi2)
fm.LayerControl(collapsed=False).add_to(inst_ubi2)

inst_ubi2
Make this Notebook Trusted to load map: File -> Trust Notebook

4. Generate a Circle map and plot only institutes with more applicants than vacancies. Make the radius= ratio_applicants_vacancies * 10000 . Make these circles of color green. Name the layer of circles as Applicant Surplus. Use FeatureGroup#

#Generamos el mapa
ubication3 = inst['nlat_ie'].mean(), inst['nlong_ie'].mean() 

inst_ubi3 = fm.Map(location = ubication3, zoom_start=6)

# Crear un objeto FeatureGroup para los círculos
circulos = fm.FeatureGroup(name='Applicant Surplus')

# Iteramos sobre las ubicaciones en el GeoDataFrame y creamos un marcador para cada una
for index, row in inst.iterrows():
    if row['Clusters'] == 'Applicant Surplus':
        fm.Circle(
        location=[row['nlat_ie'], row['nlong_ie']],
        radius=row['Ratio Postulantes/Vacantes'] * 10000,
        color='forestgreen',
    ).add_to(circulos)

# Agregamos el control
circulos.add_to(inst_ubi3)
fm.LayerControl(collapsed=False).add_to(inst_ubi3)
# Mostramos el mapa
inst_ubi3
Make this Notebook Trusted to load map: File -> Trust Notebook

5. Generate a Circle map and plot only institutes with less applicants than vacancies. Make the radius= ratio_applicants_vacancies * 10000 . Make these circles of color red. Name the layer of circles as Applicant Deficit. Use FeatureGroup.#

#Generamos el mapa
ubication4 = inst['nlat_ie'].mean(), inst['nlong_ie'].mean() 

inst_ubi4 = fm.Map(location = ubication4, zoom_start=6)

# Crear un objeto FeatureGroup para los círculos
circulo_2 = fm.FeatureGroup(name='Applicant Deficit')

# Iteramos sobre las ubicaciones en el GeoDataFrame y creamos un marcador para cada una
for index, row in inst.iterrows():
    if row['Clusters'] == 'Applicant Deficit':
        fm.Circle(
        location=[row['nlat_ie'], row['nlong_ie']],
        radius=row['Ratio Postulantes/Vacantes'] * 10000,
        color='red',
    ).add_to(circulo_2)

# Agregamos el control
circulo_2.add_to(inst_ubi4)
fm.LayerControl(collapsed=False).add_to(inst_ubi4)
# Mostramos el mapa
inst_ubi4
Make this Notebook Trusted to load map: File -> Trust Notebook

6. Generate a Circle Map that combines map 4 and 5 into a unique map. Use FeatureGroup. Additionally, generate a legend for this map indicating the color and label.#

#Generamos el mapa
ubication5 = inst['nlat_ie'].mean(), inst['nlong_ie'].mean() 

inst_ubi5 = fm.Map(location = ubication5, zoom_start=6)

# Crear un objeto FeatureGroup para los círculos
app_surp = fm.FeatureGroup(name='Applicant Surplus')
app_def = fm.FeatureGroup(name='Applicant Deficit')

# Iteramos sobre las ubicaciones en el GeoDataFrame y creamos un marcador para cada una
for index, row in inst.iterrows():
    if row['Clusters'] == 'Applicant Surplus':
        fm.Circle(
        location=[row['nlat_ie'], row['nlong_ie']],
        radius=row['Ratio Postulantes/Vacantes'] * 10000,
        color='green').add_to(app_surp)
    else:
        fm.Circle(
        location=[row['nlat_ie'], row['nlong_ie']],
        radius=row['Ratio Postulantes/Vacantes'] * 10000,
        color='red').add_to(app_def)

# Agregamos el control
app_surp.add_to(inst_ubi5)
app_def.add_to(inst_ubi5)
fm.LayerControl(collapsed=False).add_to(inst_ubi5)

# Creamos la leyenda
legend_html_0 = '''
<div style="
position: fixed; 
bottom: 10px;
left: 10px;
width: 200px;
height: 120px;
z-index:9999;
font-size:14px;
<p><a style="color:black;font-size:100%;margin-left:5px;">&#9658;</a>&emsp;<b>Legend</b></p>
<p><a style="color:green;font-size:100%;margin-left:5px;">&#9679;</a>&emsp;<b>Applicant Surplus</b></p>
<p><a style="color:red;font-size:100%;margin-left:5px;">&#9679;</a>&emsp;<b>Applicant Deficit</b></p>


</div>
<div style="
position: fixed; 
bottom: 10px;
left: 10px;
width: 200px;
height: 120px; 
z-index:9998;
font-size:14px;
background-color: #ffffff;

opacity: 0.7;
">
</div>
'''

legend_html = "{% macro html(this, kwargs) %}" + legend_html_0 + "{% endmacro %}"
legend = br.element.MacroElement()
legend._template = br.element.Template(legend_html)
inst_ubi5.get_root().add_child(legend)

# Mostramos el mapa
inst_ubi5
Make this Notebook Trusted to load map: File -> Trust Notebook